﻿using System;
using System.Collections.Generic;
using System.Data.SqlClient;
using System.Diagnostics;
using System.IO;
using System.Linq;
using System.Text;
using System.Transactions;
using AzureDiagMon.Common;
using AzureDiagMon.Common.Interfaces;
using AzureDiagMon.DatabaseLayer.Common;

namespace AzureDiagMon.DB.SqlServer
{
    public class SqlServerDatabaseLayer : DatabaseLayer.Common.DatabaseLayer
    {
        #region Overrides of DatabaseLayer

        protected override void InitInternal()
        {
            Debug.WriteLine("Initializing SQL Server database");

            if (EntityConnectionStringBuilder.Provider != "System.Data.SqlClient")
            {
                throw new InvalidDataProviderException(String.Format("Invalid data provider: {0}",
                                                                     EntityConnectionStringBuilder.Provider));
            }

            if (!DatabaseExist())
            {
                Debug.WriteLine("Database not found. Create new...");
                CreateDatabase();
                Debug.WriteLine("Database created.");
            }

            CheckConnection();

        }

        private void CheckConnection()
        {
            Debug.WriteLine("Checking database connection...");
            using (var conn = new SqlConnection(ProviderConnectionStringBuilder.ConnectionString))
            {
                conn.Open();
                Debug.WriteLine("Database connected.");
                conn.Close();
            }
        }

        private void CreateDatabase()
        {
            var dbName = GetInitialCatalog();
            var csb = GetMasterConnection();

            using (var conn = new SqlConnection(csb.ConnectionString))
            {
                var sql = String.Format("CREATE DATABASE [{0}]", dbName);
                using (var sqlCmd = new SqlCommand(sql, conn))
                {
                    conn.Open();
                    sqlCmd.ExecuteNonQuery();
                    conn.Close();
                }
            }

            Debug.WriteLine(String.Format("Database created: {0}", dbName));

            Debug.WriteLine("Updating schema...");
            try
            {
                using (var transaction = new TransactionScope())
                {
                    using (var conn = new SqlConnection(ProviderConnectionStringBuilder.ConnectionString))
                    {
                        conn.Open();
                        var statements = GetDatabaseCreationScriptStatements();
                        foreach (var statement in statements)
                        {
                            try
                            {
                                var strippedStatement = StripSqlStatement(statement);
                                Debug.WriteLine(strippedStatement);
                                try
                                {
                                    var cmd = new SqlCommand(strippedStatement, conn);
                                    cmd.ExecuteNonQuery();
                                    Debug.WriteLine("OK");
                                }
                                catch (Exception ex)
                                {
                                    //Skip not exist exceptions on drop statements
                                    if (strippedStatement.TrimStart().StartsWith("DROP"))
                                    {
                                        if (ex.Message.StartsWith("The specified table does not exist."))
                                        {
                                            continue;
                                        }
                                    }
                                    throw;
                                }
                            }
                            catch (Exception ex)
                            {
                                conn.Close();
                                throw new Exception(
                                    String.Format("Error while executing the following statement: {0}", statement), ex);
                            }
                        }
                    }
                    transaction.Complete();
                    Debug.WriteLine("Update script executed successfully.");
                }
            }
            catch (Exception ex)
            {
                try
                {
                    using (var conn = new SqlConnection(csb.ConnectionString))
                    {
                        var sql = String.Format("DROP DATABASE [{0}]", dbName);
                        using (var sqlCmd = new SqlCommand(sql, conn))
                        {
                            conn.Open();
                            sqlCmd.ExecuteNonQuery();
                            conn.Close();
                        }
                    }
                }
                catch (Exception dropEx)
                {
                    Trace.WriteLine(String.Format("Error dropping database: {0}", dropEx));
                }

                throw;
            }
        }

        private string GetInitialCatalog()
        {
            object result;
            if (!ProviderConnectionStringBuilder.TryGetValue("initial catalog", out result))
                throw new Exception("Database not specified in provider connection string.");
            return (string)result;
        }

        private SqlConnectionStringBuilder GetMasterConnection()
        {
            var result = new SqlConnectionStringBuilder(ProviderConnectionStringBuilder.ConnectionString);
            result.Remove("initial catalog");
            return result;
        }

        private bool DatabaseExist()
        {
            var dbName = GetInitialCatalog();
            var csb = GetMasterConnection();

            using (var conn = new SqlConnection(csb.ConnectionString))
            {
                using (var sqlCmd = new SqlCommand("SELECT database_id FROM sys.databases WHERE Name = @dbName", conn))
                {
                    sqlCmd.Parameters.AddWithValue("@dbName", dbName);

                    conn.Open();
                    var databaseId = sqlCmd.ExecuteScalar();
                    conn.Close();

                    return databaseId != null && (int)databaseId > 0;

                }
            }
        }

        #endregion
    }
}
